package com.carreirati.evaluation.domain;

import java.util.List;
import org.springframework.roo.addon.dbre.RooDbManaged;
import org.springframework.roo.addon.javabean.RooJavaBean;
import org.springframework.roo.addon.jpa.activerecord.RooJpaActiveRecord;
import org.springframework.roo.addon.json.RooJson;
import org.springframework.roo.addon.tostring.RooToString;
import org.springframework.transaction.annotation.Transactional;

@RooJavaBean
@RooJpaActiveRecord(identifierType = AppraisalResultsPK.class, versionField = "", table = "appraisal_results", schema = "dbo")
@RooDbManaged(automaticallyDelete = true)
@RooJson
public class AppraisalResults {

    public static long countAppraisalResultses() {
        return entityManager().createQuery("SELECT COUNT(*) FROM AppraisalResults o", Long.class).getSingleResult();
    }

    public static List<com.carreirati.evaluation.domain.AppraisalResults> findAppraisalResultById(long appraisalId, long questionId) {
        return entityManager().createNativeQuery("SELECT * FROM appraisal_results WHERE appraisal_Id = '" + appraisalId + "' AND question_Id ='" + questionId + "'", AppraisalResults.class).getResultList();
    }
  
    @Transactional
    public void save(long appraisalId, long questionId, long[] chosenParametersId) {
        if (entityManager == null) entityManager = entityManager();
        entityManager.flush();
        String sqlString = ""; 
        //first remove the old values
        sqlString = "DELETE FROM Appraisal_results WHERE appraisal_Id = '"+ appraisalId +"' AND question_Id='"+ questionId  +"'";
        entityManager.createNativeQuery(sqlString).executeUpdate();
        
        //insert the new values
        for (int i=0;i<chosenParametersId.length;i++){
	        sqlString = "INSERT INTO Appraisal_results VALUES('"+ appraisalId +"','"+ questionId +"','"+ chosenParametersId[i] +"')";
	   	    entityManager.createNativeQuery(sqlString).executeUpdate();
	   	    entityManager.flush();
        }    
        if (chosenParametersId.length > 0){
	        // update grade in apraisal relationship
	        sqlString = "UPDATE dbo.appraisal_relationship  SET grade = (SELECT SUM(p.weight) FROM  dbo.appraisal_results AS ar INNER JOIN dbo.parameter AS p ON "; 
	        		sqlString += "ar.chosen_question_param_id = p.id AND ar.appraisal_id = dbo.appraisal_relationship.appraisal_id AND ";
	        		sqlString += "ar.question_id = dbo.appraisal_relationship.question_id GROUP BY ar.appraisal_id, ar.question_id) ";
	        		sqlString += "WHERE appraisal_id = '"+ appraisalId +"' and question_id = '"+ questionId +"' ";
	        
	        entityManager.createNativeQuery(sqlString).executeUpdate();
	     	entityManager.flush();   
        } else  {
        	 // update grade in apraisal relationship
	        sqlString = "UPDATE dbo.appraisal_relationship  SET grade = null ";  
	        sqlString += "WHERE appraisal_id = '"+ appraisalId +"' and question_id = '"+ questionId +"' ";
	        
	        entityManager.createNativeQuery(sqlString).executeUpdate();
	     	entityManager.flush();   
        }
    }

    
}
